#include "widget.h"
#include "ui_widget.h"
#include <QAxObject>
#include <QMessageBox>
#include <QSqlError>
#include <QSqlRecord>
#include<QFileDialog>
#include <QDebug>
#include <QDir>

Widget::Widget(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::Widget)
{
    ui->setupUi(this);

    connectAllSignals();
}

Widget::~Widget()
{
    delete ui;
}

void Widget::showTableString(const QStringList table)
{
    if(table.isEmpty())
        return;
    QTableWidget*tableWidget=ui->tableWidget;
    QStringList header=table.at(0).simplified().split(' ',QString::SkipEmptyParts);
    tableWidget->setRowCount(table.count()-1);
    tableWidget->setColumnCount(header.count());
    tableWidget->setHorizontalHeaderLabels(header);

    for(int i=1;i<table.count();i++)
    {
        QStringList line=table.at(i).split('\t',QString::KeepEmptyParts);
        for(int j=0;j<header.count();j++)
        {
            QTableWidgetItem*item=new QTableWidgetItem;
            item->setText(line.at(j));
            item->setTextAlignment(Qt::AlignCenter);
            tableWidget->setItem(i-1,j,item);
        }
    }
}

void Widget::connectAllSignals()
{
    connect(ui->openE,&QPushButton::clicked,this,&Widget::OpenExcel);
    connect(ui->saveE,&QPushButton::clicked,this,&Widget::SaveExcel);
    connect(ui->openSQL,&QPushButton::clicked,this,&Widget::OpenSQL);
}

void Widget::OpenExcel()
{
    //获取要读取得文件名
    QString fileName=QFileDialog::getOpenFileName(this,"读取","/C++/*.xlsx");
    if(fileName.isEmpty())
        return;
    fileName=QDir::toNativeSeparators(fileName);

    //打开Excel进程、获取工作簿、工作表、单元格
    QAxObject*myExcel=new QAxObject("Excel.application",this);
    myExcel->setProperty("DisplayAlerts",false);
    QAxObject*workBooks=myExcel->querySubObject("WorkBooks");
    workBooks->dynamicCall("Open(const QString&)",fileName);
    QAxObject*workBook=myExcel->querySubObject("ActiveWorkBook");
    QAxObject*mySheets=workBook->querySubObject("Sheets");
    QAxObject*sheet=mySheets->querySubObject("Item(int)",1);

    //获取已经使用的单元格区域，并得到行列数
    QAxObject*range=sheet->querySubObject("UsedRange");
    QAxObject*rows=range->querySubObject("Rows");
    QAxObject*colums=range->querySubObject("Columns");
    int count_row=rows->dynamicCall("Count").toUInt();
    int count_col=colums->dynamicCall("Count").toUInt();

    //提取单元格中内容，存放到StringList中
    QStringList tableString;
    QAxObject*cell;
    tableString.clear();
    for(int i=1;i<=count_row;i++)
    {
        QString line;
        for(int j=1;j<=count_col;j++)
        {
            cell=range->querySubObject("Cells(int,int)",i,j);
            line+=cell->dynamicCall("Value").toString()+"\t";
        }
        tableString.append(line);
    }

    //关闭工作簿、结束进程
    workBook->dynamicCall("Close()");
    myExcel->dynamicCall("Quit()");

    //显示得到的数据
    showTableString(tableString);
}

void Widget::SaveExcel()
{
    //获取要保存的文件名
    QString fileName=QFileDialog::getSaveFileName(this,"保存","1","*.xlsx");
    if(fileName.isEmpty())
        return;
    fileName=QDir::toNativeSeparators(fileName);

    //获取表格行列数
    QTableWidget*table=ui->tableWidget;
    int rows=table->rowCount();
    int columns=table->columnCount();

    //获取表头
    QStringList header;
    for(int i=0;i<columns;i++)
        header.append(table->horizontalHeaderItem(i)->text());

    //打开Excel进程，获取工作簿集合
    QAxObject*myExcell=new QAxObject("Excel.application",this);
    myExcell->setProperty("DisplayAlerts",false);
    QAxObject*workbooks=myExcell->querySubObject("WorkBooks");
    workbooks->dynamicCall("Add");

    //获取当前工作簿、工作表集合
    QAxObject*workbook=myExcell->querySubObject("ActiveWorkBook");
    QAxObject*sheets=workbook->querySubObject("Sheets");

    //获取当前工作表、设置表名称“Hello”
    QAxObject*sheet=sheets->querySubObject("Item(int)",1);
//    sheet->setProperty("Name","Hello");

    //获取单元格
    //保存表头
    QAxObject*cell;
    for(int i=0;i<header.count();i++)
    {
        cell=sheet->querySubObject("Cells(int,int)",1,i+1);
        cell->setProperty("Value",header.at(i));
    }

    //保存表内容
    for(int i=0;i<rows;i++)
    {
        for(int j=0;j<table->columnCount();j++)
        {
            QTableWidgetItem*item=table->item(i,j);
            cell=sheet->querySubObject("Cells(int,int)",i+2,j+1);
            cell->setProperty("Value",item->text());
        }
    }

    //保存工作簿
    workbook->dynamicCall("SaveAs(QString)",fileName);

    //关闭工作簿、结束进程
    workbook->dynamicCall("Close()");
    myExcell->dynamicCall("Quit()");

    QMessageBox::information(this,"信息","保存成功");
}

void Widget::OpenSQL()
{
    db = QSqlDatabase::addDatabase("QMYSQL");//添加mysql
        db.setHostName("192.168.138.133");
        db.setUserName("root");
        db.setPassword("123456");
        db.setDatabaseName("test268");
        db.setPort(3306);
        if(!db.open())
        {
            qDebug() << db.lastError() <<endl;
            return ;
        }
        qDebug() << "打开成功\n";
        QSqlQuery query;
        if(query.exec("select * from test;")==false)
        {
            qDebug()<<db.lastError()<<endl;
            return;
        }

        int columnCount = query.record().count();
            QStringList headers;
            for (int i = 0; i < columnCount; ++i)
            {
                headers << query.record().fieldName(i);
            }
            ui->tableWidget->setColumnCount(columnCount);
            ui->tableWidget->setHorizontalHeaderLabels(headers);
            ui->tableWidget->setRowCount(0);

            int currentRow = 0;
            while (query.next())
            {
                QList<QTableWidgetItem*> items;
                for (int col = 0; col < columnCount; ++col)
                {
                    items << new QTableWidgetItem(query.value(col).toString());
                }
                ui->tableWidget->insertRow(currentRow);
                for (int col = 0; col < columnCount; ++col)
                {
                    ui->tableWidget->setItem(currentRow, col, items[col]);
                }
                currentRow++;
            }


//        QStringList headers{"序号", "姓名", "出生年月", "职称及时间", "最后学历及时间", "最后学位及时间", "毕业学校", "任教学科", "任教时间"};

//        ui->tableWidget->setColumnCount(9);
//        ui->tableWidget->setHorizontalHeaderLabels(headers);
//        ui->tableWidget->setRowCount(0);

//        int currentRow = 0;
//        while (query.next())
//        {
//            QList<QTableWidgetItem*> items;
//            items << new QTableWidgetItem(query.value("序号").toString())
//                  << new QTableWidgetItem(query.value("姓名").toString())
//                  << new QTableWidgetItem(query.value("出生年月").toString())
//                  << new QTableWidgetItem(query.value("职称及时间").toString())
//                  << new QTableWidgetItem(query.value("最后学历及时间").toString())
//                  << new QTableWidgetItem(query.value("最后学位及时间").toString())
//                  << new QTableWidgetItem(query.value("毕业学校").toString())
//                  << new QTableWidgetItem(query.value("任教学科").toString())
//                  << new QTableWidgetItem(query.value("任教时间").toString());

//            ui->tableWidget->insertRow(currentRow);
//            for (int col = 0; col < 9; ++col)
//            {
//                ui->tableWidget->setItem(currentRow, col, items[col]);
//            }
//            currentRow++;
//        }

}
